USE [OctoNetApps]
GO

/****** Object:  StoredProcedure [classified].[ClassifiedCategories_Delete]    Script Date: 05/25/2011 07:01:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE PROCEDURE [classified].[ClassifiedCategories_Delete]
	@ResourceID bigint,
	@CategoryID bigint
AS
BEGIN

	SET NOCOUNT ON;

BEGIN TRY
	
		BEGIN TRANSACTION TR

		DECLARE @Categories TABLE 
		(
			ID bigint
		)
		
		INSERT INTO @Categories
		SELECT CategoryID FROM classified.ClassifiedCategories
		WHERE ResourceID = @ResourceID AND 
			(CategoryID = @CategoryID OR
			ParentCategoryID = @CategoryID OR
			ParentCategoryID IN (SELECT CategoryID FROM classified.ClassifiedCategories WHERE ResourceID = @ResourceID AND ParentCategoryID = @CategoryID))
		
		DELETE FROM classified.ClassifiedAdsCustomAttributeValues
		WHERE CategoryID IN (SELECT ID FROM @Categories)
		
		DELETE FROM classified.ClassifiedAdsAttributeValues
		WHERE CategoryID IN (SELECT ID FROM @Categories)
		
		DELETE FROM classified.ClassifiedAttributeValues
		WHERE CategoryID IN (SELECT ID FROM @Categories)
		
		DELETE FROM classified.ClassifiedAttributes
		WHERE CategoryID IN (SELECT ID FROM @Categories)
		
		INSERT INTO classified.ExpiredClassifiedAds
		SELECT [AdID]
			  ,[ResourceID]
			  ,[CategoryID]
			  ,[PostTime]
			  ,DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
			  ,[Searching]
			  ,[Title]
			  ,[AdText]
			  ,[TextFormat]
			  ,[Location]
			  ,[PriceType]
			  ,[Price]
			  ,[UserID]
			  ,[UserComputerID]
			  ,[UserIP]
		FROM classified.ClassifiedAds WHERE ResourceID = @ResourceID AND CategoryID IN (SELECT ID FROM @Categories)
		
		DELETE FROM classified.ClassifiedAds
		WHERE ResourceID = @ResourceID AND CategoryID IN (SELECT ID FROM @Categories)
		
		DELETE FROM classified.ClassifiedCategories
		WHERE CategoryID IN (SELECT ID FROM @Categories)
			
		UPDATE classified.ClassifiedResources
		SET CategoryTreeVersion = CategoryTreeVersion+1
	
	
	COMMIT TRANSACTION TR
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION TR
END CATCH
	
END




GO


